﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using KaoWuYouLibs.Common;
using KaoWuYouLibs.Model;

namespace KaoWuYouLibs.Dal
{
    public class TestPaperAccess
    {
        public List<TestPaperModel> FindTestPapersBySubjectIDAndSubclassID(int SubjectID, int SubclassID)
        {
            SqlConnection conn = new SqlConnection(Tools.ConnString);
            try
            {
                conn.Open();
                List<TestPaperModel> re = new List<TestPaperModel>();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = @"SELECT * FROM TestPaper WHERE (SubjectID=@SubjectID)AND(SubclassID=@SubclassID)";
                cmd.Parameters.AddRange(new SqlParameter[] 
                {
                    new SqlParameter("@SubjectID", SubjectID),
                    new SqlParameter("@SubclassID", SubclassID)
                });
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    TestPaperModel tmp = new TestPaperModel()
                    {
                        SubclassID = Convert.ToInt32(sdr["SubclassID"]),
                        SubjectID = Convert.ToInt32(sdr["SubjectID"]),
                        TestPaperID = Convert.ToInt32(sdr["TestPaperID"]),
                        TestPaperName = sdr["TestPaperName"] as string
                    };
                    re.Add(tmp);
                }

                return re;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

        public TestPaperModel FindTestPaper(int TestPaperID)
        {
            SqlConnection conn = new SqlConnection(Tools.ConnString);
            try
            {
                conn.Open();
                TestPaperModel re = new TestPaperModel();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = @"SELECT * FROM TestPaper WHERE TestPaperID=@TestPaperID";
                cmd.Parameters.AddRange(new SqlParameter[] 
                {
                    new SqlParameter("@TestPaperID", TestPaperID)
                });
                SqlDataReader sdr = cmd.ExecuteReader();
                if (sdr.Read())
                    re = new TestPaperModel()
                   {
                       SubclassID = Convert.ToInt32(sdr["SubclassID"]),
                       SubjectID = Convert.ToInt32(sdr["SubjectID"]),
                       TestPaperID = Convert.ToInt32(sdr["TestPaperID"]),
                       TestPaperName = sdr["TestPaperName"] as string
                   };

                return re;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

        public int AddTestPaper(string TestPaperName, int SubjectID, int SubClassID)
        {
            SqlConnection conn = new SqlConnection(Tools.ConnString);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = @"INSERT INTO TestPaper(TestPaperName,SubjectID,SubClassID) 
                 VALUES(@TestPaperName,@SubjectID,@SubClassID) SELECT @@IDENTITY";
                cmd.Parameters.AddRange(new SqlParameter[]
                    {
                        new SqlParameter("@TestPaperName", TestPaperName),
                        new SqlParameter("@SubjectID", SubjectID),
                        new SqlParameter("@SubClassID", SubClassID)
                    });

                return Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (Exception ex)
            {
                return -1;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
